﻿--获取我的网站列表
CREATE proc [dbo].[proc_SiteForm_GetEmployeeList]
(
	@startIndex int,
	@endIndex int,
	@docount bit,
	@UserName nvarchar(50),
	@stateId int,
	@CustomerName nvarchar(50)
) 
as
set nocount on
	if(@docount=1)
		begin
			select sf.id,sf.CustomerId,sf.CustomerName,sf.OrderId,sf.ProductId,'' as ProductName,sf.ContactPerson,sf.ContactTel,sf.WebSitePrice,sf.SignDate,sf.StateId,'' as StateName,sf.Description,sf.CreateTime,sp.CreateTime as AssignTime from siteform sf inner join SitePersonnel sp on sf.id = sp.sid
			where sp.UserName = @UserName and (case @stateId when 0 then 0 else sf.stateId end) = @stateId and sf.CustomerName like '%'+@CustomerName+'%'
		end
		else
		begin
		declare @indextable table(id int identity(1,1),nid int)
		set rowcount @endIndex
		insert into @indextable(nid) select sf.id from siteform sf inner join SitePersonnel sp on sf.id = sp.sid where sp.UserName = @UserName and (case @stateId when 0 then 0 else sf.stateId end) = @stateId and sf.CustomerName like '%'+@CustomerName+'%'
	select 
		sf.id,
		sf.OrderId,
		sf.ProductId,
		(select title from product p where sf.ProductId=p.Id) as ProductName,
		sf.CustomerId,
		sf.CustomerName,
		sf.ContactPerson,
		sf.ContactTel,
		sf.SignDate,
		sf.WebSitePrice,
		sf.Description,
		sf.CreateTime,
		sf.StateId,
		(select ss.title from ProjectState ss where ss.ProjectId=sf.stateid) as StateName,
		sp.CreateTime as AssignTime
	from siteform sf inner join SitePersonnel sp on sf.id = sp.sid and  sp.UserName = @UserName and (case @stateId when 0 then 0 else sf.stateId end) = @stateId and sf.CustomerName like '%'+@CustomerName+'%'
			 inner join @indextable t on sf.id=t.nid

	where 
			t.id between @startIndex and @endIndex 
		order by t.id
		end
	set nocount off
